"= NULL" is not the same as "IS NULL"

Hi,

I managed to fix my write conflict problems in Ms Access. Now I already
stumble upon another issue:

I have a psqlODBC linked table 'tarticles' in my Ms Access 2000 application,
and a form 'Artikels' to make changes to this table. One peculiarity of this
form is, that one of the varchar fields gets set programmatically.

For example, when I create a new record, and save it (by moving to a new empty
record), I find this in the commlog:
conn=995a250, query='INSERT INTO "public"."tarticles"
("vendorid","reference","category","type","islot","amount"," invoicecurrency",
"invoiceprice","priceunit","pricecustomer","invoiceid","chec k","label","imgname","amountlot")
VALUES
(235,'00-00007',2,7,'1','100',1,'100','1','0',921,'0','Diver se',NULL,'100')'

The field imgname was set to NULL programmatically.
When I move one record back in the form, to the record that I just added, it
will show #deleted# in every field.

This move back is represented in the commlog by:
conn=995a250, query='declare "SQL_CUR0995ED90" cursor with hold for
SELECT "public"."tarticles"."articleid" FROM "public"."tarticles"
WHERE "vendorid" = 235 AND "reference" = '00-00007' AND "category" = 2
AND "type" = 7 AND "islot" = '1' AND "amount" = '100' AND "invoicecurrency" =
1 AND "invoiceprice" = '100' AND "priceunit" = '1' AND "pricecustomer" = '0'
AND "invoiceid" = 921 AND "check" = '0' AND "label" = 'Diverse' AND
"imgname" = NULL AND "amountlot" = '100''

I tried this query directly in phpPgSQL, and indeed it returns no results. On
the other hand, if I modify "imgname" = NULL to "imgname" IS NULL, the query
works fine.

I found references via google that '= NULL' and 'IS NULL' are not the same
thing as far as PostgreSQL is concerned, which I understand.
They even provide a runtime command to override this:
SET transform_null_equals TO ON;
Unfortunatly it seems this should be called before each query that is
affected. It's not set globally.

So I'm not sure how I can solve this issue. I could work around it and make
sure no NULL values can happen, but I wonder if there might be an easier way
by means of a preference setting in Access or psqlODBC or PostgreSQL.


Has anybody else stumbled upon this issue ?


Thanks,

Geert
--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel : +32 479 339 655
Email: info@kobaltwit.be

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: "= NULL" is not the same as "IS NULL"

Geert Janssens wrote:

> I tried this query directly in phpPgSQL, and indeed it returns no results. On
> the other hand, if I modify "imgname" = NULL to "imgname" IS NULL, the query
> works fine.
[...]
> Has anybody else stumbled upon this issue ?

I'm afraid so, in Joe Celko's "SQL for Smarties" book, after which I simply
accepted that that was the way SQL worked. He also comments on the IS TRUE etc.
form which I see PostgreSQL now supports, also in the relevant section of the
manual I see IS DISTINCT FROM which might help.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: "= NULL" is not the same as "IS NULL"

> I'm afraid so, in Joe Celko's "SQL for Smarties" book, after which I si=
mply
> accepted that that was the way SQL worked.

Yes for what I recall from my reading that he mentions that there are spe=
cific reasons to allow
nulls. During Table design, if one cannot see how these specific rules a=
pply to the data to of a
column, then the column should be constrained as NOT NULL, with a DEFAULT=
value as '{left blank}'
or 0 or some other value that would produce this effect.

But I am sure that this is just one particular philosophy of table design=
..

Regards,

Richard Broersma Jr.=20

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: "= NULL" is not the same as "IS NULL"

--0__=0ABBFB72DFE4BF538f9e8a93df938690918c0ABBFB72DFE4BF53
Content-type: multipart/alternative;
Boundary="1__=0ABBFB72DFE4BF538f9e8a93df938690918c0ABBFB72DF E4BF53"

--1__=0ABBFB72DFE4BF538f9e8a93df938690918c0ABBFB72DFE4BF53
Content-type: text/plain; charset="us-ascii"
Content-transfer-encoding: quoted-printable


I have a question for the mailing list.
More generally, let us assume that when in Access, you change a record
focus (after a delete, insert, update. upon opening a form, or moving the
Access cursor), it may be reasonable to execute one or more SELECT queries
to refresh the form.
Why does it tend to use the WHERE clause to match each field value, instead
of a primary key for a match? What are the conditions that force primary
key usages vs. "match every field" syntax?

And it appears Geert may have found a bug perhaps (in pgODBC or in Access)?
Where the WHERE clause match for NULL values tries to use an =3DNULL syntax
instead of IS NULL .
When I turn on ODBC tracing , from ODBC Administrator -- outside of the
pgODBC DSN, the trace shows parameterized ODBC syntax. I am not sure if
the basic query (for example:
"SELECT ? WHERE emp_id=3D? AND customer_id=3D? and item_code=3D?" comes from
Access/Jet or from the pgODBC driver. It seems like it would come from the
client (Access) and mere mortals would be helpless to change it. The fact
that a parameter has a NULL value (resulting in perhaps customer =3DNULL,
which of course is not valid SQL) does not change that basic query. A fix
would require the driver to understand a NULL value and change the syntax
from =3DNULL or <>NULL to IS NULL or NOT IS NULL on the fly.

I am afraid I might be speculating over my head. I really am seeking to
understand. So anybody who can shed light on these things please pitch in.




Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com



=
=20
Geert Janssens =
=20
=20
e> To=
=20
Sent by: pgsql-odbc@postgresql.org =
=20
pgsql-odbc-owner@ cc=
=20
postgresql.org =
=20
Subject=
=20
[ODBC] '=3D NULL' is not the same as=
=20
09/06/2006 09:55 'IS NULL' =
=20
=
=20
=
=20
=
=20
=
=20
=
=20
=
=20




Hi,

I managed to fix my write conflict problems in Ms Access. Now I already
stumble upon another issue:

I have a psqlODBC linked table 'tarticles' in my Ms Access 2000
application,
and a form 'Artikels' to make changes to this table. One peculiarity of
this
form is, that one of the varchar fields gets set programmatically.

For example, when I create a new record, and save it (by moving to a new
empty
record), I find this in the commlog:
conn=3D995a250, query=3D'INSERT INTO "public"."tarticles"
("vendorid","reference","category","type","islot","amount"," invoicecurrency=
",

"invoiceprice","priceunit","pricecustomer","invoiceid","chec k","label","img=
name","amountlot")

VALUES
(235,'00-00007',2,7,'1','100',1,'100','1','0',921,'0','Diver se',NULL,'100')'


The field imgname was set to NULL programmatically.
When I move one record back in the form, to the record that I just added,
it
will show #deleted# in every field.

This move back is represented in the commlog by:
conn=3D995a250, query=3D'declare "SQL_CUR0995ED90" cursor with hold for
SELECT "public"."tarticles"."articleid" FROM "public"."tarticles"
WHERE "vendorid" =3D 235 AND "reference" =3D '00-00007' AND "category" =3D 2
AND "type" =3D 7 AND "islot" =3D '1' AND "amount" =3D '100' AND "invoicecur=
rency"
=3D
1 AND "invoiceprice" =3D '100' AND "priceunit" =3D '1' AND "pricecustomer" =
=3D
'0'
AND "invoiceid" =3D 921 AND "check" =3D '0' AND "label" =3D 'Diverse' AND
"imgname" =3D NULL AND "amountlot" =3D '100''

I tried this query directly in phpPgSQL, and indeed it returns no results.
On
the other hand, if I modify "imgname" =3D NULL to "imgname" IS NULL, the
query
works fine.

I found references via google that '=3D NULL' and 'IS NULL' are not the same
thing as far as PostgreSQL is concerned, which I understand.
They even provide a runtime command to override this:
SET transform_null_equals TO ON;
Unfortunatly it seems this should be called before each query that is
affected. It's not set globally.

So I'm not sure how I can solve this issue. I could work around it and make

sure no NULL values can happen, but I wonder if there might be an easier
way
by means of a preference setting in Access or psqlODBC or PostgreSQL.


Has anybody else stumbled upon this issue ?


Thanks,

Geert
--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel : +32 479 339 655
Email: info@kobaltwit.be

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--1__=0ABBFB72DFE4BF538f9e8a93df938690918c0ABBFB72DFE4BF53
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

I have a question for the mailing list.

More generally, let us assume that when in Access, you change a record focu=
s (after a delete, insert, update. upon opening a form, or moving the Acces=
s cursor), it may be reasonable to execute one or more SELECT queries to re=
fresh the form.

Why does it tend to use the WHERE clause to match each field value, instead=
of a primary key for a match? What are the conditions that force primary k=
ey usages vs. "match every field" syntax?



And it appears Geert may have found a bug perhaps (in pgODBC or in Access)?=
Where the WHERE clause match for NULL values tries to use an =3DNULL synta=
x instead of IS NULL .

When I turn on ODBC tracing , from ODBC Administrator -- outside of the pgO=
DBC DSN, the trace shows parameterized ODBC syntax. I am not sure if the =
basic query (for example:

"SELECT ? WHERE emp_id=3D? AND customer_id=3D? and item_code=3D?"=
comes from Access/Jet or from the pgODBC driver. It seems like it would co=
me from the client (Access) and mere mortals would be helpless to change it=
.. The fact that a parameter has a NULL value (resulting in perhaps customer=
=3DNULL, which of course is not valid SQL) does not change that basic quer=
y. A fix would require the driver to understand a NULL value and change the=
syntax from =3DNULL or <>NULL to IS NULL or NOT IS NULL on the fly.<=
br>


I am afraid I might be speculating over my head. I really am seeking to und=
erstand. So anybody who can shed light on these things please pitch in.









Greg Campbell ENG-ASE/Michelin US5

Lexington, South Carolina

803-951-5561, x75561

Fax: 803-951-5531

greg.campbell@us.michelin.com



6" height=3D"16" alt=3D"Inactive hide details for Geert Janssens <info@k=
obaltwit.be>">Geert Janssens <info@kobaltwit.be>

4BF538f9e8a93df9@michelin.com); background-repeat: no-repeat; " width=3D"40= %"> Geert Janssens <info@kobaltwit.be>= Sent by: pgsql-odbc-owner@postgresql.org 09/06/2006 09:55

= 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> To "> =3D"0" height=3D"1" width=3D"1" alt=3D""> pgsql-odbc@postgresql.org 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> cc "> =3D"0" height=3D"1" width=3D"1" alt=3D""> 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> Subject "100%"> der=3D"0" height=3D"1" width=3D"1" alt=3D""> [ODBC] '=3D NULL' is not the same as 'IS NULL' 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> td> helin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D"">

Re: "= NULL" is not the same as "IS NULL"

greg.campbell@us.michelin.com wrote:
> I have a question for the mailing list.
> More generally, let us assume that when in Access, you change a record
> focus (after a delete, insert, update. upon opening a form, or moving the
> Access cursor), it may be reasonable to execute one or more SELECT queries
> to refresh the form.
> Why does it tend to use the WHERE clause to match each field value, instead
> of a primary key for a match? What are the conditions that force primary
> key usages vs. "match every field" syntax?
>

From ancient times it's programmers' routine work to verify if the
target record was changed
or not before updat/deleting the record. MS seems to prefer the way to
verify and update/delete
at a time and executes an update/delete query with WHERE clause to
match each field value.
If the query update/deletes exactly one row it means that both the
verification and the execution
are OK. If it update/deletes no row it probably meas that someone
changed the record somewhere.
IMHO it's a lot simpler than the way to get the record with lock and
verify the change by the
program by itself and update/delete the record.

regarsds,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: "= NULL" is not the same as "IS NULL"

Hi greg,

greg.campbell@us.michelin.com wrote:
> Thanks Hiroshi for the insight and the history lesson.
>
> In light of this I decided to further my education by doing some testing
> against a simple table.
>
> CREATE TABLE test
> (
> emp_id serial NOT NULL,
> last_name varchar(20) NOT NULL,
> first_name varchar(20) NOT NULL,
> "level" int4,
> CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
> )
>
> In my table I have a field named "level" which is allowed to be NULL.
>
> I set the DSN to do row versioning, and ran the pgODBC CommLog at the same
> time as the ODBC Trace (because a good masochist loves to observe the
> statement handles).
> In general I did not find the MSAcess to do WHERE match querying before an
> update, but it did do one after an update to apparently count the number of
> rows, to see if what it just updated took hold.
>
I can see insert examples but can't see update ones here.
Insert operations are pretty different from update ones.
We have little to do before insert operations.

> There is a general pattern of
> 1.DML Executable (INSERT, UPDATE)
> 2. Select on primary_key = NULL (should fetch 0 rows)
> 3. Commit
> 4. Select on primary_key = NULL (should fetch 0 rows)
> 5. Select with the WHERE (exact match each non NULL field) to count the
> exact match, should be 1 ? or more?
> 6. Select using the primary key, (used to refresh the screen I think.)
>

I understand what MSACCESS means a little but not wholly.
> CommLog===============================================
> conn=147538008, query='INSERT INTO "public"."test"
> ("last_name","first_name") VALUES ('Rubble','Barney')'
>

The serious problem here is that the primary key was not given in the
above INSERT statement.
Firstly MSAccess guesses the primary key might have been NULL because
ommitted values
are generally NULL (MSAccess doesn't know inside PostgreSQL at all) and
issues the following
command.

> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin" FROM "public"."test"
> WHERE "emp_id" IS NULL'
> [ fetched 0 rows ]
> conn=147538008, query='COMMIT'
>
I don't know why MSAccess tries the same operation twice.


> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin" FROM "public"."test"
> WHERE "emp_id" IS NULL'
> [ fetched 0 rows ]
>
Here MSAccess gives up the direct primary key approach and searches key
of the
inserted record using known item values expecting that the matching
records are a few.
> conn=147538008, query='SELECT "public"."test"."emp_id" FROM "public"."test"
> WHERE "last_name" = 'Rubble' AND "first_name" = 'Barney''
> [ fetched 1 rows ]
>

Fortunately just 1 record was returned in this example.
What MSAccess has really wanted to know was the whole content of the
inserted record.
So it issues the following command finally.

> conn=147538008, query='SELECT
> "emp_id","last_name","first_name","level","xmin" FROM "public"."test"
> WHERE "emp_id" = 3'
> [ fetched 1 rows ]

regards,
Hirshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings